﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.Xml.Serialization;
using System.Runtime.Serialization;
using System.Text;
using ParieurPro.DAL.DatabaseModel;
using ParieurPro.DAL.Exception;
using System.Data.SqlClient;

namespace ParieurPro.DAL
{

    /// <summary>
    /// Classe de gestion des classements des joueurs
    /// </summary>
    [Serializable()]
    public class RankObject
    {
        #region Enum

        public enum RankType 
        { 
            Overall=0, 
            Average=-1
        } ;
        
        #endregion

        #region Attributes
        //private string _friendlyName;
        private int _customContestId;
        private long _totalScore;
        private int _score;
        private int? _goodPredictionCount;
        private int? _goodScoreCount;
        private int _eventCount;
        private int _matchCount;
        private int _rank;
        private Guid _userId;
        private string _userName;
        private int? _rankDifferential;
        private Double _averageScore;
        private int? _bestOfEventCount;
        private int? _bestOfEventInProgressCount;
        private byte? _isBestScore;
        private DateTime? _lastBetUpdate;
        #endregion

        #region Properties

        /// <summary>
        /// Identifiant unique du joueur
        /// </summary>
        [Key()]
        [DataMemberAttribute()]
        public Guid UserId
        {
            get { return _userId; }
            set { _userId = value; }
        }

        /// <summary>
        /// Login du joueur
        /// </summary>
        [DataMemberAttribute()]
        public string UserName
        {
            get { return _userName; }
            set { _userName = value; }
        }

        ///// <summary>
        ///// 
        ///// </summary>
        //[DataMemberAttribute()]
        //public string FriendlyName
        //{
        //    get { return _friendlyName; }
        //    set { _friendlyName = value; }
        //}

        /// <summary>
        /// Classement du joueur
        /// </summary>
        [DataMemberAttribute()]
        public int Rank
        {
            get { return _rank; }
            set { _rank = value; }
        }

        /// <summary>
        /// Identifiant du tournoi
        /// </summary>
        [DataMemberAttribute()]
        public int CustomContestId
        {
            get { return _customContestId; }
            set { _customContestId = value; }
        }

        /// <summary>
        /// Score global
        /// </summary>
        [DataMemberAttribute()]
        public long TotalScore
        {
            get { return _totalScore; }
            set { _totalScore = value; }
        }

        /// <summary>
        /// Score de l'événement courant
        /// </summary>
        [DataMemberAttribute()]
        public int Score
        {
            get { return _score; }
            set { _score = value; }
        }

        /// <summary>
        /// Nombre de bonnes tendances
        /// </summary>
        [DataMemberAttribute()]
        public int? GoodPredictionCount
        {
            get { return _goodPredictionCount; }
            set { _goodPredictionCount = value; }
        }

        /// <summary>
        /// Nombre de bons scores
        /// </summary>
        [DataMemberAttribute()]
        public int? GoodScoreCount
        {
            get { return _goodScoreCount; }
            set { _goodScoreCount = value; }
        }

        /// <summary>
        /// Nombre d'événements auxquels le joueur a 
        /// participé
        /// </summary>
        [DataMemberAttribute()]
        public int EventCount
        {
            get { return _eventCount; }
            set { _eventCount = value; }
        }

        /// <summary>
        /// Nombre de matchs pariés
        /// </summary>
        [DataMemberAttribute()]
        public int MatchCount
        {
            get { return _matchCount; }
            set { _matchCount = value; }
        }

        /// <summary>
        /// Ecart par rapport au dernier classement
        /// </summary>
        [DataMemberAttribute()]
        public int? RankDifferential
        {
            get { return _rankDifferential; }
            set { _rankDifferential = value; }
        }

        /// <summary>
        /// Score moyen
        /// </summary>
        [DataMemberAttribute()]
        public Double AverageScore
        {
            get { return _averageScore; }
            set { _averageScore = value; }
        }

        /// <summary>
        /// Nombre de fois premier d'une journée terminée
        /// </summary>
        [DataMemberAttribute()]
        public int? BestOfEventCount
        {
            get { return _bestOfEventCount; }
            set { _bestOfEventCount = value; }
        }

        /// <summary>
        /// Nombre de fois premier dans une journée (encore en cours)
        /// </summary>
        [DataMemberAttribute()]
        public int? BestOfEventInProgressCount
        {
            get { return _bestOfEventInProgressCount; }
            set { _bestOfEventInProgressCount = value; }
        }

        /// <summary>
        /// Indique si c'est le meilleur score de la journée
        /// </summary>
        [DataMemberAttribute()]
        public byte? IsBestScore
        {
            get { return _isBestScore; }
            set { _isBestScore = value; }
        }

        [DataMemberAttribute()]
        public DateTime? LastBetUpdate
        {
            get { return _lastBetUpdate; }
            set { _lastBetUpdate = value; }
        }
        
        #endregion

        /// <summary>
        /// Renvoie le classement d'un tournoi
        /// </summary>
        /// <param name="dbContext">Connexion à la base de données</param>
        /// <param name="customContestId">Identifiant du tournoi</param>
        /// <returns>Liste des classements triée par score (décroissant), bonnes tendances (décroissant), ordre alphabétique des logins des joueurs</returns>
        /// <exception cref="ArgumentException">dbContext vaut null</exception>
        public static List<RankObject> GetRanksInDatabase(MyBetDBEntities dbContext, int customContestId)
        {

            // Vérification des préconditions
            if (dbContext == null)
            {
                throw new ArgumentException("RankObject.GetRanksInDatabase - dbContext can not be null");
            }

            object[] parameters = 
                        {
                           new SqlParameter { ParameterName = "p0", Value = customContestId }
                        };

            IEnumerable<RankObject> ranksResult = dbContext.ExecuteStoreQuery<RankObject>
                (String.Format(@"select rnk.RankId,rnk.TotalScore, rnk.EventCount, rnk.GoodPredictionCount,  
                rnk.GoodScoreCount, rnk.MatchCount, rnk.CurrentRank, isnull(rnk.RankDifferential,0) as RankDifferential, s.SubscriptionId, au.UserName, au.UserId, isnull(
                (SELECT SUM(CustomContestEventScores.IsBestScore) 
                    FROM CustomContestEventScores
                    WHERE CustomContestEventScores.UserId = s.UserId and CustomContestEventScores.isCompleted=1 and CustomContestEventScores.CustomContestId=@p0),0) as BestOfEventCount,
                    isnull((SELECT SUM(CustomContestEventScores.IsBestScore) 
                    FROM CustomContestEventScores
                    WHERE CustomContestEventScores.UserId = s.UserId and CustomContestEventScores.isCompleted=0 and CustomContestEventScores.CustomContestId=@p0),0) as BestOfEventInProgressCount
                    from Ranks rnk 
                   left join Subscriptions s on s.SubscriptionId=rnk.SubscriptionId
                   left join aspnet_Users au on au.UserId = s.UserId
                   where s.CustomContestId=@p0 ORDER BY rnk.TotalScore DESC, rnk.GoodPredictionCount DESC, au.UserName"), parameters);

            // Renvoi la liste
            List<RankObject> contestRanks = ranksResult.ToList();
            return contestRanks;
        }

        /// <summary>
        /// Renvoie le classement des scores moyens des joueurs d'un tournoi
        /// </summary>
        /// <param name="dbContext">Connexion à la base de données</param>
        /// <param name="customContestId">Identifiant du tournoi</param>
        /// <returns>Liste des classements triée par score moyen (décroissant), ordre alphabétique des logins des joueurs</returns>
        /// <exception cref="ArgumentException">dbContext vaut null</exception>
        public static List<RankObject> GetAverageRanksInDatabase(MyBetDBEntities dbContext, int customContestId)
        {
            // Vérification des préconditions
            if (dbContext == null)
            {
                throw new ArgumentException("RankObject.GetAverageRanksInDatabase - dbContext can not be null");
            }

            object[] parameters = 
                        {
                           new SqlParameter { ParameterName = "p0", Value = customContestId }
                        };

            IEnumerable<RankObject> ranksResult = dbContext.ExecuteStoreQuery<RankObject>
                (String.Format(@"select au.UserName,au.UserId,  AVG(cast(evtScores.Score as Float)) as AverageScore 
                                ,rnk.EventCount
                                from [EventScores] evtScores
                                left join [aspnet_Users] au on au.UserId = evtScores.UserId
                                left join [Events] evt on evt.EventId = evtScores.EventId
                                left join [Subscriptions] sub on sub.UserId = evtScores.UserId
                                left join [Ranks] rnk on rnk.SubscriptionId = sub.SubscriptionId
                                left join [CustomContestLinks] ccl on ccl.CustomContestId = @p0
                                left join [CustomContestEventScores] cces on cces.EventId=evtScores.EventId and cces.UserId=evtScores.UserId
                                where evt.ContestId=ccl.ContestId and sub.CustomContestId=@p0 and cces.CustomContestId=@p0
                                and evtScores.EventId in (
                                SELECT EventId from [Matchs] m
                                left join [UserBets] ub on ub.MatchId = m.MatchId
                                where ub.UserId = evtScores.UserId
                                )
                                group by au.UserName,au.UserId ,rnk.EventCount
                                ORDER BY AverageScore DESC, au.UserName;"), parameters);

            List<RankObject> contestRanks = ranksResult.ToList();
            return contestRanks;
        }

        /// <summary>
        /// Renvoie le classement d'un événement pour un tournoi donné
        /// </summary>
        /// <param name="dbContext">Connexion à la base de données</param>
        /// <param name="customContestId">Identifiant du tournoi</param>
        /// <param name="eventId">Identifiant de l'événement</param>
        /// <returns>Classement par ordre des scores (décroissant), login des utilisateurs</returns>
        /// <exception cref="ArgumentException">dbContext vaut null</exception>
        public static List<RankObject> GetRanksInDatabaseFromEvent(MyBetDBEntities dbContext, int customContestId, int eventId)
        {
            // Vérification des préconditions
            if (dbContext == null)
            {
                throw new ArgumentException("RankObject.GetRanksInDatabaseFromEvent - dbContext can not be null");
            }

            object[] parameters = 
                        {
                           new SqlParameter { ParameterName = "p0", Value = customContestId },
                           new SqlParameter { ParameterName = "p1", Value = eventId }
                        };
            IEnumerable<RankObject> ranksResult = dbContext.ExecuteStoreQuery<RankObject>
                (String.Format(@"select au.UserName, au.UserId, isnull(evtScores.Score,0) as Score, isnull(evtScores.GoodPredictionCount,0) as GoodPredictionCount, isnull(evtScores.GoodScoreCount,0) as GoodScoreCount, isnull(cces.IsBestScore,0) as IsBestScore, isnull(cces.CurrentRank,0) as Rank from [EventScores] evtScores
                                 left join [CustomContestEventScores] cces on cces.EventId=evtScores.EventId and cces.UserId=evtScores.UserId
                                 left join [aspnet_Users] au on au.UserId = evtScores.UserId
                                 left join [Events] evt on evt.EventId = evtScores.EventId
                                 left join [CustomContestLinks] ccl on ccl.CustomContestId = @p0
                                 where evt.ContestId=ccl.ContestId and evtScores.EventId=@p1 and cces.CustomContestId=@p0 ORDER BY evtScores.Score DESC, au.UserName;"), parameters);

            List<RankObject> contestRanks = ranksResult.ToList();
            return contestRanks;
        }

        /// <summary>
        /// Renvoie le classement d'un événement pour un tournoi donné et un joueur donné
        /// </summary>
        /// <param name="dbContext">Connexion à la base de données</param>
        /// <param name="customContestId">Identifiant du tournoi</param>
        /// <param name="eventId">Identifiant de l'événement</param>
        /// <returns>Classement par ordre des scores (décroissant), login des utilisateurs</returns>
        /// <exception cref="ArgumentException">dbContext vaut null</exception>
        public static RankObject GetUserRankInDatabaseFromEvent(MyBetDBEntities dbContext, int customContestId, Guid userId, int eventId)
        {
            // Vérification des préconditions
            if (dbContext == null)
            {
                throw new ArgumentException("RankObject.GetRanksInDatabaseFromEvent - dbContext can not be null");
            }

            object[] parameters = 
                        {
                           new SqlParameter { ParameterName = "p0", Value = customContestId },
                           new SqlParameter { ParameterName = "p1", Value = eventId },
                           new SqlParameter { ParameterName = "p2", Value = userId }
                        };
            IEnumerable<RankObject> ranksResult = dbContext.ExecuteStoreQuery<RankObject>
                (String.Format(@"select au.UserName, au.UserId, evtScores.LastBetUpdate,  isnull(evtScores.Score,0) as Score, isnull(evtScores.GoodPredictionCount,0) as GoodPredictionCount, isnull(evtScores.GoodScoreCount,0) as GoodScoreCount, isnull(cces.IsBestScore,0) as IsBestScore, isnull(cces.CurrentRank,0) as Rank from [EventScores] evtScores
                                 left join [CustomContestEventScores] cces on cces.EventId=evtScores.EventId and cces.UserId=evtScores.UserId
                                 left join [aspnet_Users] au on au.UserId = evtScores.UserId
                                 left join [Events] evt on evt.EventId = evtScores.EventId
                                 left join [CustomContestLinks] ccl on ccl.CustomContestId = @p0
                                 where evt.ContestId=ccl.ContestId and evtScores.EventId=@p1 and cces.CustomContestId=@p0 and au.UserId=@p2 ORDER BY evtScores.Score DESC, au.UserName;"), parameters);

            RankObject contestRank = ranksResult.FirstOrDefault();
            return contestRank;
        }

        /// <summary>
        /// Renvoie tous les événements dont les paris sont terminés et avec un classement
        /// pour un tournoi donné
        /// </summary>
        /// <param name="dbContext">Connexion à la base de données</param>
        /// <param name="customContestId">Identifiant du tournoi</param>
        /// <returns>Liste des événements dont les paris sont clos et dont le classement a été mis à jour</returns>
        /// <exception cref="ArgumentException">dbContext vaut null</exception>
        public static List<EventObject> GetEventsWithRankFromCustomContestId(MyBetDBEntities dbContext, int customContestId)
        {
            // Vérification des préconditions
            if (dbContext == null)
            {
                throw new ArgumentException("RankObject.GetEventsInDatabase - dbContext can not be null");
            }

            object[] parameters = 
                        {
                           new SqlParameter { ParameterName = "p0", Value = customContestId }
                        };

            IEnumerable<EventObject> eventsResult = dbContext.ExecuteStoreQuery<EventObject>
                (String.Format(@"SELECT distinct evtScores.[EventId], evt.[DayNumber], evt.CloseDate
                                 FROM [EventScores] evtScores
                                 LEFT JOIN [Events] evt on evt.[EventId]=evtScores.[EventId]
                                 left join [CustomContestLinks] ccl on ccl.CustomContestId = @p0
                                 WHERE evt.ContestId=ccl.ContestId and evt.CloseDate<SYSUTCDATETIME() ORDER BY evt.CloseDate DESC;"), parameters);

            List<EventObject> contestEvents = eventsResult.ToList();
            return contestEvents;
        }

        public static bool AddOrUpdateFromObject(MyBetDBEntities dbContext, RankObject rank, int subscriptionId, int currentRank, int? rankDifferential)
        {
            MyBetDBEntities db = dbContext;
            try
            {
                var query = (from rnk in db.Ranks
                             where rnk.SubscriptionId == subscriptionId
                             select rnk);
                IList<Ranks> existingRank = query.ToList();

                if (existingRank.Count < 1)
                {
                    // Add
                    Ranks rnk = new Ranks();
                    rnk.EventCount = rank.EventCount;
                    if (rank.GoodPredictionCount.HasValue)
                    {
                        rnk.GoodPredictionCount = rank.GoodPredictionCount.Value;
                    }
                    if (rank.GoodScoreCount.HasValue)
                    {
                        rnk.GoodScoreCount = rank.GoodScoreCount.Value;
                    }
                    rnk.MatchCount = rank.MatchCount;
                    rnk.SubscriptionId = subscriptionId;
                    rnk.TotalScore = rank.TotalScore;
                    rnk.CurrentRank = currentRank;
                    rnk.RankDifferential = rankDifferential;
                    db.Ranks.AddObject(rnk);
                    dbContext.SaveChanges();
                    return true;
                }
                else
                {
                    // Update
                    existingRank[0].EventCount = rank.EventCount;
                    if (rank.GoodPredictionCount.HasValue)
                    {
                        existingRank[0].GoodPredictionCount = rank.GoodPredictionCount.Value;
                    }
                    if (rank.GoodScoreCount.HasValue)
                    {
                        existingRank[0].GoodScoreCount = rank.GoodScoreCount.Value;
                    }
                    existingRank[0].MatchCount = rank.MatchCount;
                    existingRank[0].SubscriptionId = subscriptionId;
                    existingRank[0].TotalScore = rank.TotalScore;
                    existingRank[0].CurrentRank = currentRank;
                    existingRank[0].RankDifferential = rankDifferential;
                    //dbContext.SaveChanges();
                    return true;
                }
            }
            catch (System.Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Création ou mise à jour d'un classement
        /// </summary>
        /// <param name="dbContext">Connexion à la base de données</param>
        /// <param name="rank">Classement à créer ou mettre à jour</param>
        /// <returns></returns>
        /// <exception cref="ArgumentException">dbContext vaut null</exception>
        /// <exception cref="ArgumentException">rank vaut null</exception>
        public static void AddOrUpdate(MyBetDBEntities dbContext, Ranks rank)
        {

            // Vérification des préconditions
            if (dbContext == null)
            {
                throw new ArgumentException("RankObject.AddOrUpdate - dbContext can not be null");
            }
            if (rank == null)
            {
                throw new ArgumentException("RankObject.AddOrUpdate - rank can not be null");
            }

            var query = (from rnk in dbContext.Ranks
                             where rnk.SubscriptionId == rank.SubscriptionId
                             select rnk);
                Ranks existingRank = query.FirstOrDefault();

                if (existingRank==null)
                {
                    // Add
                    dbContext.Ranks.AddObject(rank);
                }
                else
                {
                    // Update
                    existingRank.EventCount = rank.EventCount;
                    existingRank.GoodPredictionCount = rank.GoodPredictionCount;
                    existingRank.GoodScoreCount = rank.GoodScoreCount;
                    existingRank.MatchCount = rank.MatchCount;
                    existingRank.SubscriptionId = rank.SubscriptionId;
                    existingRank.TotalScore = rank.TotalScore;
                    existingRank.CurrentRank = rank.CurrentRank;
                    existingRank.RankDifferential = rank.RankDifferential;
                }

        }

        /// <summary>
        /// Renvoie le classement de l'abonné passé en paramètre
        /// </summary>
        /// <param name="dbContext">Connexion à la base de données</param>
        /// <param name="subscriptionId">Identifiant de l'abonné</param>
        /// <returns>Classement de l'abonné</returns>
        /// <exception cref="ArgumentException">dbContext vaut null</exception>
        public static Ranks GetRank(MyBetDBEntities dbContext, int subscriptionId)
        {
            var query = (from rnk in dbContext.Ranks
                         where rnk.SubscriptionId == subscriptionId
                         select rnk);
            Ranks currentRank = query.FirstOrDefault();
            return currentRank;
        }

        /// <summary>
        /// Renvoie le classement d'une journée d'un tournoi donné
        /// </summary>
        /// <param name="dbContext">Connexion à la base de données</param>
        /// <param name="customContestId">Identifiant du tournoi</param>
        /// <param name="eventId">Identifiant de l'événement</param>
        /// <returns>Classements des joueurs abonnés au tournoi</returns>
        /// <exception cref="ArgumentException">dbContext vaut null</exception>
        public static List<RankObject> GetCustomContestRanksFromEvent(MyBetDBEntities dbContext, int customContestId, int eventId)
        {

            // Vérification des préconditions
            if (dbContext == null)
            {
                throw new ArgumentException("RankObject.GetCustomContestRanksFromEvent - dbContext can not be null");
            }

            List<RankObject> rankList = new List<RankObject>();
            List<RankObject> rankViewList;
            int rank = 1;

            if (eventId == Convert.ToInt32(RankType.Overall))
            {
                // Classement general
                rankViewList = RankObject.GetRanksInDatabase(dbContext, customContestId);
            }
            else if (eventId == Convert.ToInt32(RankType.Average))
            {
                // Moyenne des scores des journées
                rankViewList = RankObject.GetAverageRanksInDatabase(dbContext, customContestId);
            }
            else
            {
                // Classement d'un événement
                rankViewList = RankObject.GetRanksInDatabaseFromEvent(dbContext, customContestId, eventId);
            }

            // Création de la liste des classements des abonnés
            foreach (RankObject rnk in rankViewList)
            {
                rnk.Rank = rank;
                if (rnk.Score != 0)
                {
                    rnk.TotalScore = rnk.Score;
                }
                if (rnk.EventCount == 0)
                {
                    rnk.EventCount = 1;
                }
                rankList.Add(rnk);
                rank++;
            }

            return rankList;
        }
    }
}